<?php
/**
 * Product: ASW.Database.
 * Date: 2023-09-10
 * Time: 15:08
 */

namespace ASW\Database\SqlAdapters;


use ASW\Database\SqlAdapter;

class MySQLAdapter extends SqlAdapter
{

    private function buildInsertSqlParts(array $insertData): array|false
    {
        $insertFieldArray = [];
        $insertValueArray = [];

        foreach ($insertData as $field => $val) {
            $insertFieldArray[] = "`$field`";

            if (!is_scalar($val)) $val = json_encode($val, JSON_UNESCAPED_UNICODE);
            if (is_string($val)) {
                if (str_starts_with($val, '::')) {
                    // "::NOW()"
                    $insertValueArray[] = substr($val, 2);
                } elseif (str_starts_with($val, ':') && array_key_exists(substr($val, 1), $this->_params)) {
                    // ":PARAM"
                    $insertValueArray[] = $val;
                } else {
                    $trueParamName                 = "i___" . count($this->_params);
                    $this->_params[$trueParamName] = $val;
                    $insertValueArray[]            = ":$trueParamName";
                }
            } elseif (is_bool($val)) {
                $insertValueArray[] = $val ? 'true' : 'false';
            } else {
                $insertValueArray[] = $val;
            }
        }

        if (empty($insertFieldArray)) return false;
        $insertFieldSql = implode(', ', $insertFieldArray);
        $insertValueSql = implode(', ', $insertValueArray);

        return [
            'fieldsPart' => $insertFieldSql,
            'valuesPart' => $insertValueSql,
        ];
    }

    private function buildUpdateSql(array $updateData): string
    {
        $updateArray = [];
        foreach ($updateData as $field => $val) {
            if (!is_scalar($val)) $val = json_encode($val, JSON_UNESCAPED_UNICODE);
            if (is_string($val)) {
                if (str_starts_with($val, '::')) {
                    // "::NOW()"
                    $updateArray[] = "`$field` = " . substr($val, 2);
                } elseif (str_starts_with($val, ':') && array_key_exists(substr($val, 1), $this->_params)) {
                    // ":PARAM"
                    $updateArray[] = "`$field` = $val";
                } else {
                    $trueParamName                 = "u___" . count($this->_params);
                    $updateArray[]                 = "`$field` = :$trueParamName";
                    $this->_params[$trueParamName] = $val;
                }
            } elseif (is_bool($val)) {
                $trueValue     = $val ? 'true' : 'false';
                $updateArray[] = "`$field` = $trueValue";
            } else {
                $updateArray[] = "`$field` = $val";
            }
        }
        return implode(', ', $updateArray);
    }

    /**
     * @inheritDoc
     */
    public function generateInsertSql(array $insertData, bool $ignoreError = false, string $onDuplicateKey = ''): string
    {
        if (false === $insertSqlParts = $this->buildInsertSqlParts($insertData)) return '';
        $insertFieldSql = $insertSqlParts['fieldsPart'];
        $insertValueSql = $insertSqlParts['valuesPart'];

        $ignoreSql      = $ignoreError ? "IGNORE" : "";
        $onDuplicateSql = empty($onDuplicateKey) ? "" : "ON DUPLICATE KEY $onDuplicateKey";

        return "INSERT $ignoreSql INTO $this->_tableName ($insertFieldSql) VALUES ($insertValueSql) $onDuplicateSql;";
    }

    public function generateUpsertSql(array $insertData, array $updateData, string $uniqueKey = ''): string
    {
        if (false === $insertSqlParts = $this->buildInsertSqlParts($insertData)) return '';
        $insertFieldSql = $insertSqlParts['fieldsPart'];
        $insertValueSql = $insertSqlParts['valuesPart'];

        if (empty($updateData)) foreach ($insertData as $field => $value) $updateData[$field] = "::values($field)";
        $updateSql = $this->buildUpdateSql($updateData);
        return "INSERT INTO $this->_tableName ($insertFieldSql) VALUES ($insertValueSql) ON DUPLICATE KEY UPDATE $updateSql;";
    }

    /**
     * @inheritDoc
     */
    public function generateMultiInsertSql(array $insertDataList, bool $ignoreError = false, string $onDuplicateKey = ""): string
    {
        if (count($insertDataList) == 0) return '';

        $firstRow         = $insertDataList[0];
        $insertFieldArray = array_keys($firstRow);
        if (empty($insertFieldArray)) return '';

        foreach ($insertFieldArray as &$fieldName) $fieldName = "`$fieldName`";
        $fieldSQL = implode(',', $insertFieldArray);
        $fieldSQL = "($fieldSQL)";

        $rowValueSQLArray = [];
        foreach ($insertDataList as $row) {
            $rowValueArray = array_values($row);
            foreach ($rowValueArray as &$rowValue) {
                if (!is_scalar($rowValue)) $rowValue = json_encode($rowValue, JSON_UNESCAPED_UNICODE);
                if (is_string($rowValue)) {
                    if (str_starts_with($rowValue, '::')) {
                        $rowValue = substr($rowValue, 2);
                    } else {
                        $rowValue = str_replace("\\", "\\\\", $rowValue);
                        $rowValue = str_replace("'", "\'", $rowValue);
                        $rowValue = "'$rowValue'";
                    }
                } elseif (is_bool($rowValue)) {
                    $rowValue = $rowValue ? 'true' : 'false';
                }
            }
            $rowValueSQL = implode(',', $rowValueArray);
            $rowValueSQL = "($rowValueSQL)";

            $rowValueSQLArray[] = $rowValueSQL;
        }
        $valueSQL = implode(', ', $rowValueSQLArray);

        $ignoreSQL      = $ignoreError ? "IGNORE" : "";
        $onDuplicateKey = empty($onDuplicateKey) ? "" : "ON DUPLICATE KEY $onDuplicateKey";

        return "INSERT $ignoreSQL INTO {$this -> _tableName} $fieldSQL VALUES $valueSQL $onDuplicateKey;";
    }


    /**
     * @inheritDoc
     */
    public function generateUpdateSql(array $updateData): string
    {
        if (empty($updateData)) return '';
        $updateSql = $this->buildUpdateSql($updateData);

        $where   = empty($this->_where) ? "" : "WHERE " . $this->_where;
        $orderBy = empty($this->_orderBy) ? '' : 'ORDER BY ' . $this->_orderBy;
        $limit   = $this->_pageSize <= 0 ? '' : 'LIMIT ' . $this->_pageSize;

        return "UPDATE $this->_tableName $this->_join SET $updateSql $where $orderBy $limit;";
    }

    /**
     * @inheritDoc
     */
    public function generateDeleteSql(array $otherTableNames = []): string
    {
        $fromTableNames = implode(',', $otherTableNames);

        $where   = empty($this->_where) ? '' : "WHERE " . $this->_where;
        $orderBy = empty($this->_orderBy) ? '' : 'ORDER BY ' . $this->_orderBy;

        $limit = '';
        if ($this->_pageSize > 0) {
            if ($this->_pageIndex <= 0) $this->_pageIndex = 1;
            $pageStart = ($this->_pageIndex - 1) * $this->_pageSize;
            $limit     = "LIMIT $pageStart, $this->_pageSize";
        }

        return "DELETE $fromTableNames FROM $this->_tableName $this->_join $where $orderBy $limit;";
    }

    /**
     * @inheritDoc
     */
    public function generateSelectSql(): string
    {
        $groupBy = empty($this->_groupBy) ? '' : 'GROUP BY ' . $this->_groupBy;
        $fields  = empty($this->_fields) ? '*' : $this->_fields;

        $where   = empty($this->_where) ? '' : 'WHERE ' . $this->_where;
        $orderBy = empty($this->_orderBy) ? '' : 'ORDER BY ' . $this->_orderBy;

        $limit = '';
        if ($this->_pageSize > 0) {
            if ($this->_pageIndex <= 0) $this->_pageIndex = 1;
            $pageStart = ($this->_pageIndex - 1) * $this->_pageSize;
            $limit     = "LIMIT $pageStart, $this->_pageSize";
        }

        return "SELECT $fields FROM $this->_tableName $this->_join $where $groupBy $orderBy $limit;";
    }

    /**
     * 判断表是否存在
     *
     * @param string $tableName
     *
     * @return bool
     */
    public function tableExists(string $tableName = ''): bool
    {
        $trueTableName = empty($tableName) ? $this->_tableName : $tableName;
        if (false === $queryStatement = $this->query("SHOW TABLES LIKE '$trueTableName';")) return false;
        return !empty($queryStatement->fetchAll());
    }

    /**
     * 删除表
     *
     * @param string $tableName
     *
     * @return bool
     */
    public function tableDrop(string $tableName = ''): bool
    {
        $trueTableName = empty($tableName) ? $this->_tableName : $tableName;
        return $this->query("DROP TABLE '$trueTableName';") !== false;
    }

    /**
     * 根据通配符取表名列表
     *
     * @param string $tableNameMatch
     *
     * @return string[]|false
     */
    public function getTableNamesLike(string $tableNameMatch = ''): array|false
    {
        $trueTableNameMatch = empty($tableNameMatch) ? $this->_tableName : $tableNameMatch;
        if (false === $queryStatement = $this->query("SHOW TABLES LIKE '$trueTableNameMatch';")) return false;
        return $queryStatement->fetchColumn();
    }

    /**
     * 拷贝数据到新表
     *
     * @param string $distTableName
     *
     * @return bool
     */
    public function tableCopyTo(string $distTableName): bool
    {
        $operation = $this->tableExists($distTableName) ? "INSERT" : "CREATE TABLE";
        $selectSql = $this->generateSelectSql();
        return $this->query("$operation $distTableName $selectSql") !== false;
    }

    /**
     * 拷贝表结构到新表
     *
     * @param string $newTableName
     * @param bool $rebuildIfExists
     *
     * @return bool
     */
    public function tableCopyStructureTo(string $newTableName, bool $rebuildIfExists = false): bool
    {
        if (empty($newTableName)) return false;
        $createSql = "";
        if ($this->tableExists($newTableName)) {
            if (!$rebuildIfExists) return false;
            $createSql = "DROP TABLE IF EXISTS $newTableName;";
        }

        if (false === $queryStatement = $this->query("SHOW CREATE TABLE $this->_tableName")) return false;
        $ddlDataSet = $queryStatement->fetchAll(\PDO::FETCH_NUM);
        if (empty($ddlDataSet)) return false;

        $createSql = "$createSql {$ddlDataSet[0][1]};";
        $createSql = str_ireplace("CREATE TABLE $this->_tableName", "CREATE TABLE $newTableName", $createSql);
        $createSql = preg_replace("/AUTO_INCREMENT=\d+/i", "AUTO_INCREMENT=1", $createSql);

        return $this->query($createSql) !== false;
    }

    /**
     * 添加字段
     *
     * @param string $fieldName
     * @param string $afterFieldName
     * @param string $type
     * @param bool $allowNull
     * @param string|float|int|null $defaultValue
     *
     * @return bool
     */
    public function tableAddField(
        string                $fieldName,
        string                $afterFieldName = '',
        string                $type = 'varchar(255)',
        bool                  $allowNull = true,
        string|float|int|null $defaultValue = ''
    ): bool
    {
        if (empty($fieldName)) return false;
        $sqlNullPart = $allowNull ? "NULL" : "NOT NULL";
        $sqlAfter    = empty($afterFieldName) ? "" : "AFTER $afterFieldName";
        return $this->query("ALTER TABLE $this->_tableName ADD COLUMN $fieldName $type $sqlNullPart DEFAULT '$defaultValue' $sqlAfter;") !== false;
    }

    /**
     * 删除字段
     *
     * @param string $fieldName
     *
     * @return bool
     */
    public function tableDeleteField(string $fieldName): bool
    {
        if (empty($fieldName)) return false;
        return $this->query("ALTER TABLE $this->_tableName DROP COLUMN $fieldName;") !== false;
    }

    /**
     * 添加表分区
     *
     * @param string $partitionName 分区名称
     * @param mixed $maxValue 分区最大值
     *
     * @return bool
     */
    public function tableAddPartition(string $partitionName, mixed $maxValue): bool
    {
        if (empty($partitionName)) return false;
        return $this->query("ALTER TABLE $this->_tableName ADD PARTITION (PARTITION $partitionName VALUES LESS THAN ($maxValue));") !== false;
    }
}